package com.ailk.evaluation.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import org.springframework.stereotype.Component;

import com.ailk.evaluation.common.PageRequest;
import com.ailk.evaluation.common.PageResponse;
import com.ailk.evaluation.evaluation.Evaluation;
import com.ailk.evaluation.evaluation.EvaluationEntry;
import com.ailk.evaluation.evaluation.EvaluationEntryScore;
import com.ailk.evaluation.evaluation.EvaluationPlan;
import com.ailk.evaluation.evaluation.EvaluationTemplate;
import com.ailk.evaluation.evaluation.Evaluator;
import com.ailk.evaluation.manager.ParameterUtils;
import com.ailk.evaluation.sql.ParameterizedSQLFactory;
import com.ailk.evaluation.sql.SQLFactoryInterface;
import com.ailk.evaluation.sql.SingleExecutableSql;
import com.anhorn.webshare.base.dao.BaseJdbcDao;
import com.anhorn.webshare.base.util.Page;


@Component("evaluationDao")
public class EvaluationDao extends BaseJdbcDao{
	
	public List<Map> getEvaluationTemplateMainById(String id){
		String templateMainSql = "select * from EVALUATION_TEMPLATE where TEMPLATE_ID = ? ";
		List<Map> templateMainRs=this.getJdbcTemplate().queryForList(templateMainSql, new Object[]{id});
		return templateMainRs;
	}
	
	public List<Map> getEvaluationTemplateDetailById(String id){
		String templateDetailSql = "select eti.entry_id, ee.type_id, eet.type_name, ee.entry_name, ee.entry_title, eti.parent_entry_id "
				+ "from EVALUATION_TEMPLATE_ITEM eti, EVALUATION_ENTRY ee, EVALUATION_ENTRY_TYPE eet "
				+ "where eti.TEMPLATE_ID = ? "
				+ "and ee.entry_id = eti.entry_id "
				+ "and eet.type_id = ee.type_id "
				+ "order by eti.order asc";
		
		List<Map> templateDetailRs=this.getJdbcTemplate().queryForList(templateDetailSql, new Object[]{id});
		return templateDetailRs;
	}
	
	public List<Map> getEvaluationPlanMainById(String id){
		String planMainSql = "select * from EVALUATION_PLAN where PLAN_ID = ? ";
		List<Map> planMainRs=this.getJdbcTemplate().queryForList(planMainSql, new Object[]{id});
		return planMainRs;
	}
	
	public List<Map> getEvaluationPlanScoreById(String id){
		String planScoreSql = "select * from EVALUATION_PLAN_SCORE where PLAN_ID = ? ";
		List<Map> planScoreRs=this.getJdbcTemplate().queryForList(planScoreSql, new Object[]{id});
		return planScoreRs;
	}
	
	public List<Map> getEvaluationPlanMainBySubId(String id){
		String planMainSql = "select EP.PLAN_ID, EVALUATION.EVALUATION_NAME PLAN_NAME, EP.TEMPLATE_ID, EVALUATION.EVALUATION_NAME REMARK from "
							+ "(select * from EVALUATION_ITEM E "
							+ "where E.ATTR_CODE = 'EVALUATOR' "
							+ "and E.ATTR_VALUE3 = ? "
							+ "limit 0,1) EVALUATOR, EVALUATION_ITEM EI, EVALUATION, EVALUATION_PLAN EP "
							+ "where EI.EVALUATION_ID = EVALUATOR.EVALUATION_ID "
							+ "and EI.ATTR_CODE = 'PLAN' "
							+ "and EI.ATTR_VALUE1 = EVALUATOR.ATTR_VALUE1 "
							+ "and EVALUATION.EVALUATION_ID = EI.EVALUATION_ID "
							+ "and EP.PLAN_ID = EI.ATTR_VALUE2 ";
		List<Map> planMainRs=this.getJdbcTemplate().queryForList(planMainSql, new Object[]{id});
		return planMainRs;
	}
	
	public List<Map> getSubEvaluationScoreBySubId(String id){
		String planScoreSql = "select * from EVALUATION_EVALUATOR_ITEM where EVALUATION_SUB_ID = ? ";
		List<Map> planScoreRs=this.getJdbcTemplate().queryForList(planScoreSql, new Object[]{id});
		return planScoreRs;
	}
	
	public List<Map> saveEvaluationMain(Evaluation evaluation){
		String createSql = "insert into EVALUATION (EVALUATION_NAME, REMARK, OBJECT_TYPE_ID, OBJECT_ID, REMIND, RESULT_NOTICE, STATE) VALUES (?,?,?,?,?,?,?)";
		int createRsCount = this.getJdbcTemplate().update(createSql, new Object[]{evaluation.getName(), 
																		evaluation.getRemark(), 
																		evaluation.getObjTypeId(), 
																		evaluation.getObjId(), 
																		evaluation.getRemind(), 
																		evaluation.getResultNotice(), 
																		evaluation.getState()});
		if(createRsCount > 0){
			String searchSql = "select * from EVALUATION where OBJECT_TYPE_ID = ? and OBJECT_ID = ?";
			return this.getJdbcTemplate().queryForList(searchSql, new Object[]{evaluation.getObjTypeId(), evaluation.getObjId()});
		}else{
			return null;
		}
	}
	
	public List<Map> updateEvaluationMain(Evaluation evaluation){
		String updateSql = "update EVALUATION set EVALUATION_NAME = ?, REMARK = ?, REMIND = ?, RESULT_NOTICE = ?, STATE = ? where EVALUATION_ID = ?";
		int createRsCount = this.getJdbcTemplate().update(updateSql, new Object[]{evaluation.getName(), 
																		evaluation.getRemark(), 
																		evaluation.getRemind(), 
																		evaluation.getResultNotice(), 
																		evaluation.getState(),
																		evaluation.getId()});
		
		return null;

	}
	
	public List<Map> updateMyEvaluationscore(String id, EvaluationEntryScore score){
		
		String scoreSql = "update EVALUATION_EVALUATOR_ITEM set SCORE = ?, REMARK = ? where EVALUATION_SUB_ID = ? and ENTRY_ID = ?";
		int createRsCount = this.getJdbcTemplate().update(scoreSql, new Object[]{score.getScore(), 
																				score.getRemark(), 
																				id, 
																				score.getEntryId()});
		return null;
	}
	
	public List<Map> saveEvaluationItem(Evaluation evaluation){

		String evaluationId = evaluation.getId();
		HashMap<String, String> plan = evaluation.getPlan();
		Vector<String> date = evaluation.getDate();
		Vector<Evaluator> evaluator = evaluation.getEvaluator();
		
		String itemSql = "insert into EVALUATION_ITEM (EVALUATION_ID, ATTR_CODE, ATTR_VALUE1, ATTR_VALUE2, ATTR_VALUE3) VALUES (?,?,?,?,?)";
		
		for(int i = 0; i < date.size(); i++){
			this.getJdbcTemplate().update(itemSql, new Object[]{evaluationId, "DATE", date.get(i), null, null});
		}
		
		for(int i = 0; i < evaluator.size(); i++){
			this.getJdbcTemplate().update(itemSql, new Object[]{evaluationId, "EVALUATOR", evaluator.get(i).getType(), evaluator.get(i).getEmail(), null});
		}
		
		for(Iterator<String> it = plan.keySet().iterator(); it.hasNext();){
			String key = it.next();
			this.getJdbcTemplate().update(itemSql, new Object[]{evaluationId, "PLAN", key, plan.get(key), null});
		}
			
		String searchSql = "select * from EVALUATION_ITEM where EVALUATION_ID = "+evaluationId;
		return this.getJdbcTemplate().queryForList(searchSql);
	}
	
	public List<Map> saveSubEvaluation(Evaluation evaluation){

		String evaluationId = evaluation.getId();
		HashMap<String, String> plan = evaluation.getPlan();
		Vector<Evaluator> evaluator = evaluation.getEvaluator();
		
		HashMap<String, Evaluator> evaluatorSet = new HashMap<String, Evaluator>();
		
		for(int i = 0; i < evaluator.size(); i++){
			if(!evaluatorSet.containsKey(evaluator.get(i).getEmail())){
				evaluatorSet.put(evaluator.get(i).getEmail(), evaluator.get(i));
			}
		}
		
		String evaluatorMainSql = "insert into EVALUATION_EVALUATOR (EVALUATION_ID, EMAIL, STATE) VALUES (?,?,?)";
		String evaluatorSearchSql = "select EVALUATION_SUB_ID from EVALUATION_EVALUATOR where EVALUATION_ID = ? and EMAIL = ?";
		String evaluatorItemSql = "insert into EVALUATION_EVALUATOR_ITEM (EVALUATION_SUB_ID, ENTRY_ID) "
								+ "select ?, ENTRY_ID from "
								+"(select TEMPLATE_ID, ENTRY_ID from EVALUATION_TEMPLATE_ITEM where template_id = 1) E "
								+"where not exists (select 1 from EVALUATION_TEMPLATE_ITEM where TEMPLATE_ID = E.TEMPLATE_ID AND PARENT_ENTRY_ID = E.ENTRY_ID)";
		String evaluationItemSql = "update EVALUATION_ITEM set ATTR_VALUE3 = ? where EVALUATION_ID = ? and ATTR_CODE = 'EVALUATOR' and ATTR_VALUE2 = ?";
				
		for(Iterator<String> it = evaluatorSet.keySet().iterator(); it.hasNext();){
			String evaluatorEmail = it.next();
			Evaluator currentEvaluator = evaluatorSet.get(evaluatorEmail);
			this.getJdbcTemplate().update(evaluatorMainSql, new Object[]{evaluationId, evaluatorEmail, "0"});
			
			String evaluationSubId = ((Integer)((List<Map>)this.getJdbcTemplate().queryForList(evaluatorSearchSql, new Object[]{evaluationId, evaluatorEmail})).get(0).get("EVALUATION_SUB_ID")).toString();
			this.getJdbcTemplate().update(evaluatorItemSql, new Object[]{evaluationSubId});
			
			this.getJdbcTemplate().update(evaluationItemSql, new Object[]{evaluationSubId, evaluationId, evaluatorEmail});
			currentEvaluator.setSubEvaluationId(evaluationSubId);
		}
		
		String searchSql = "select * from EVALUATION_EVALUATOR where EVALUATION_ID = "+evaluationId;
		return this.getJdbcTemplate().queryForList(searchSql);
	}
	
	public void deleteEvaluationMain(String evaluationId){
		String evaluationMainSql = "delete from EVALUATION where EVALUATION_ID = ?";
		this.getJdbcTemplate().update(evaluationMainSql, new Object[]{evaluationId});
	}
	
	public void deleteEvaluationItem(String evaluationId){
		String evaluationItemSql = "delete from EVALUATION_ITEM where EVALUATION_ID = ?";
		this.getJdbcTemplate().update(evaluationItemSql, new Object[]{evaluationId});
	}

	public void deleteSubEvaluation(String evaluationId){
		String evaluationSubItemSql = "delete from EVALUATION_EVALUATOR_ITEM where EVALUATION_SUB_ID in (select EVALUATION_SUB_ID from EVALUATION_EVALUATOR where EVALUATION_ID = ?)";
		String evaluationSubMainSql = "delete from EVALUATION_EVALUATOR where EVALUATION_ID = ?";
		this.getJdbcTemplate().update(evaluationSubItemSql, new Object[]{evaluationId});
		this.getJdbcTemplate().update(evaluationSubMainSql, new Object[]{evaluationId});
	}
	
	public List<Map> getEvaluationMainById(String id){
		String evaluationMainSql = "select * from EVALUATION where EVALUATION_ID = ? ";
		List<Map> evaluationMainRs=this.getJdbcTemplate().queryForList(evaluationMainSql, new Object[]{id});
		return evaluationMainRs;
	}
	
	public List<Map> getEvaluationItemById(String id){
		String evaluationItemSql = "select * from EVALUATION_ITEM where EVALUATION_ID = ? order by ITEM_ID asc";
		List<Map> evaluationItemRs=this.getJdbcTemplate().queryForList(evaluationItemSql, new Object[]{id});
		return evaluationItemRs;
	}
	
}
